#!/bin/bash

APP=education

if [ -n "$2" ] ;then
   do_date=$2
else 
   echo "请传入日期参数"
   exit
fi 
dws_course_review_1d="
insert overwrite table ${APP}.dws_course_review_1d partition (dt='$do_date')
select
       user_id,
       course_id,
       review_stars,
       count(*) over(partition by dt,course_id) course_count_1d,
       avg(review_stars) over(partition by dt,course_id)avg_review_starts_1d,
       dt
from ${APP}.dwd_course_review_inc
where deleted=0
group by user_id, course_id, review_stars, dt;
"
dws_course_question_1d="
insert overwrite table ${APP}.dws_course_question_1d partition(dt)
select question_id,
       count(question_id) course_question_count_1d,
       sum(is_correct) course_correct_count_1d,
       dt
from ${APP}.dwd_course_exam_inc
group by dt,question_id;
"
dws_course_user_paper_1d="
with
exam as (
  select user_id,
         paper_id,
         count(user_id) user_exam_count_1d,
         sum(score) user_score_sum_1d,
         sum(duration_sec) user_time_sum_1d,
         dt
  from ${APP}.dwd_course_exam_inc
  group by user_id, paper_id, dt
),
paper as (
  select data.id,
         data.course_id
  from ${APP}.ods_test_paper_inc
  where type = 'bootstrap-insert'
     or type = 'insert'
)
insert overwrite table ${APP}.dws_course_user_paper_1d partition(dt)
select user_id,
       paper_id,
       course_id,
       user_exam_count_1d,
       user_score_sum_1d,
       user_time_sum_1d,
       dt
from exam
left join paper on exam.paper_id=paper.`id`;
"
dws_course_user_paper_rank_1d="
insert overwrite table ${APP}.dws_course_user_paper_rank_1d partition(dt)
select paper_id,
       sum(`if`(rank='F',count,0)) F_RANK_1d,
       sum(`if`(rank='E',count,0)) E_RANK_1d,
       sum(`if`(rank='D',count,0)) D_RANK_1d,
       sum(`if`(rank='C',count,0)) C_RANK_1d,
       sum(`if`(rank='B',count,0)) B_RANK_1d,
       sum(`if`(rank='A',count,0)) A_RANK_1d,
       dt
from (
  select paper_id,
         rank,
         count(*) count,
         dt
  from (
    select user_id,
           exam_id,
           paper_id,
           score,
           dt,
           case
             when score >= 0 and score < 40 then 'F'
             when score >= 40 and score < 60 then 'E'
             when score >= 60 and score < 75 then 'D'
             when score >= 75 and score < 90 then 'C'
             when score >= 90 and score < 100 then 'B'
             when score = 100 then 'A'
           end rank
    from ${APP}.dwd_course_exam_inc
    group by user_id, exam_id, paper_id, score, dt
  )rk
  group by paper_id, rank, dt
)rk2
group by paper_id, dt;
"
dws_course_user_chapter_com_1d="
insert overwrite table ${APP}.dws_course_user_chapter_com_1d partition(dt='$do_date')
select user_id,
       chapter_id,
       course_id,
       play_com,
       play_sum,
       play_time,
       pos_com,
       position_sec,
       pos_time
from (
  select play2.user_id user_id,
         play2.chapter_id chapter_id,
         play_com,
         play_sum,
         play_time,
         pro2.pos_com,
         pro2.position_sec,
         pro2.pos_time
  from(
    select user_id,
           chapter_id,
           max(play_com) play_com,
           max(play_sum) play_sum,
           max(play_time) play_time
    from(
      with
      play1 as (
        select user_id,
               chapter_id,
               play_sec,
               sum(play_sec) over (partition by user_id,chapter_id order by create_time rows between unbounded preceding and current row ) sec_sum,
               `if`((sum(play_sec) over (partition by user_id,chapter_id order by create_time rows between unbounded preceding and current row )/during_sec)>=0.9,'1','0') sec_com,
               create_time
        from ${APP}.dwd_course_chapter_play_inc
        where dt='$do_date'
      )
      select user_id,
             chapter_id,
             '1' play_com,
             min(sec_sum) play_sum,
             min(create_time) play_time
      from play1
      where sec_com='1'
      group by user_id, chapter_id
      union
      select user_id,
             chapter_id,
             '0' play_com,
             max(sec_sum) play_sum,
             max(create_time) play_time
      from play1
      where sec_com='0'
      group by user_id, chapter_id
    )aa
group by user_id, chapter_id
  )play2
  full join (
    with
    pro1 as (
      select user_id,
             chapter_id,
             position_sec,
             create_time,
             update_time,
             `if`(update_time is not null ,`if`(datediff(create_time,update_time)>0,create_time,update_time),create_time) pos_time
      from ${APP}.dwd_course_chapter_process_inc
      where dt='$do_date'
    ),
    video as (
    select data.chapter_id,
           data.during_sec
    from ${APP}.ods_video_info_inc
    where type='bootstrap-insert'
       or type='insert'
    )
    select user_id,
           pro1.chapter_id,
           `if`(position_sec/during_sec>=0.9,'1','0') pos_com,
           position_sec,
           pos_time
    from pro1
    left join video on pro1.chapter_id=video.chapter_id
  )pro2
  on play2.user_id=pro2.user_id and play2.chapter_id=pro2.chapter_id
  where play_com is not null and pos_com is not null
)t1
left join (
  select id,
         course_id
  from ${APP}.ods_chapter_info_full
  where dt = '$do_date'
)t2
on t1.chapter_id=t2.id;
"
case $1 in
"dws_course_review_1d")
    hive -e "$dws_course_review_1d"
;;
"dws_course_question_1d")
    hive -e "$dws_course_question_1d"
;;
"dws_course_user_paper_rank_1d")
    hive -e "$dws_course_user_paper_rank_1d"
;;
"dws_course_user_paper_1d")
    hive -e "$dws_course_user_paper_1d"
;;
"dws_course_user_chapter_com_1d")
    hive -e "$dws_course_user_chapter_com_1d"
;;
"all")
    hive -e "$dws_course_review_1d$dws_course_question_1d$dws_course_user_paper_rank_1d$dws_course_user_paper_1d$dws_course_user_chapter_com_1d"
;;
esac